﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using System.Net;
using System.Net.Mail;

namespace Site
{
    public partial class Managment : System.Web.UI.Page
    {
        int CountEntries(string SQL)
        {
            SqlDataSource1.SelectCommand = SQL;
            DataView View = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments());
            return View.Count;
        }

        void CleanupDB()
        {
            string GUID = Guid.NewGuid().ToString();
            string Output = "";

            System.IO.FileStream fs = new FileStream(Server.MapPath("~/" + GUID + ".csv"), FileMode.CreateNew);
            System.IO.StreamWriter sr = new StreamWriter(fs);

            SqlDataSource1.SelectCommand = "SELECT * FROM DataTable";
            DataView dvView = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments());
            foreach (DataRowView DR in dvView)
            {

                Output = "";
                Output += DR["Barcode"] + ",";
                Output += DR["SiteID"] + ",";
                Output += DR["SupersCount"] + ",";
                Output += DR["Brood"] + ",";
                Output += DR["FullFrames"] + ",";
                Output += DR["Feed"] + ",";
                Output += DR["Capped"] + ",";
                Output += DR["Feeding"] + ",";
                Output += DR["Attention"] + ",";
                Output += DR["Harvest"] + ",";
                Output += DR["Problems"] + ",";
                Output += DR["Queen"] + ",";

                Output += DR["Date"] + ",";
                Output += DR["Time"] + ",";

                Output += DR["GPS"] + ",";
                Output += DR["PID"] + "";

                sr.WriteLine(Output);
            }

            sr.Close();
            sr.Dispose();

            fs.Close();
            fs.Dispose();


            string Message = "Dear AHB Data Portal User\n";
            Message += "\n";
            Message += "A Database Cleanup was executed by 'AUTO WEB SERVICE', the following modifications where made to the database:\n";
            Message += "Record(s) removed due to invalid barcode: " + CountEntries("SELECT * FROM DataTable WHERE Barcode NOT LIKE '________'").ToString() + "\n";
            Message += "Record(s) removed due to invalid hive ID: " + CountEntries("SELECT * FROM DataTable WHERE SiteID NOT LIKE 'S____H____'").ToString() + "\n";
            Message += "Record(s) removed due to invalid GPS coords: " + CountEntries("SELECT * FROM DataTable WHERE GPS NOT LIKE '%P%S%P%'").ToString() + "\n";

            Message += "\n\n Please see the attached Database Snapshot. This is the state of the database before any changes were made to it\n";
            //Delete All Records where the barcode is not 8 chars long.
            SqlDataSource1.DeleteCommand = "DELETE FROM DataTable WHERE Barcode NOT LIKE '________'";
            SqlDataSource1.Delete();


            //S0001H0001
            SqlDataSource1.DeleteCommand = "DELETE FROM DataTable WHERE SiteID NOT LIKE 'S____H____'";
            SqlDataSource1.Delete();

            ////////////////////////////////////////////////////////////////////////////////////////////////
            // Go through list of barcodes
            // Update all blank GPS coords of barcode to last know GPS coords
            ////////////////////////////////////////////////////////////////////////////////////////////////

            SqlDataSource1.SelectCommand = "SELECT DISTINCT Barcode FROM DataTable";
            DataView View = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments());

            foreach (DataRowView drv in View) //Foreach Barcode
            {
                SqlDataSource1.SelectCommand = "SELECT Top 1 * FROM DataTable WHERE GPS<>'' AND Barcode='" + drv["Barcode"] + "' ORDER BY Date DESC";
                string GPS = (SqlDataSource1.Select(new DataSourceSelectArguments()) as DataView)[0]["GPS"].ToString(); //Get Last Known GPS Data

                SqlDataSource1.UpdateCommand = "UPDATE DataTable SET GPS = '" + GPS + "' WHERE GPS=''";
                SqlDataSource1.Update();
            }


            //31P02594574SN24P70691316
            SqlDataSource1.DeleteCommand = "DELETE FROM DataTable WHERE GPS NOT LIKE '%P%S%P%'";
            SqlDataSource1.Delete();


            SqlDataSource1.SelectCommand = "SELECT * FROM Users";
            DataView DV = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments());

            SmtpClient client = new SmtpClient();

            var smtp = new SmtpClient
            {
                Host = "africanhoneybee.net",
                Port = 26,
                EnableSsl = false,
                DeliveryMethod = SmtpDeliveryMethod.Network,
                UseDefaultCredentials = false,
                Credentials = new NetworkCredential("noreply@africanhoneybee.net", "857105")
            };

            foreach (DataRowView DRV in DV)
            {
                if (DRV["userEmail"].ToString() != null && DRV["userEmail"].ToString() != "")
                {
                    MailMessage message = new MailMessage();
                    message.From = new MailAddress("noreply@africanhoneybee.net", "AHB Webmaster");
                    message.To.Add(DRV["userEmail"].ToString());
                    message.Subject = "AHB Cleanup Notification";
                    message.Body = Message;
                    message.Attachments.Add(new Attachment(Server.MapPath("~/" + GUID + ".csv")));
                    smtp.Send(message);
                }
            }

            //File.Delete(Server.MapPath("~/" + GUID + ".csv"));
        }

        void UploadStats()
        {
            int UploadedCount = 0;
            DateTime Time = DateTime.Now;
            Time = Time.Subtract(new TimeSpan(24, 0, 0));
            SqlDataSource1.SelectCommand = "SELECT * FROM DataTable WHERE [Date]>'" + Time.ToShortDateString() + "' AND Time([Time])>'" + Time.ToShortTimeString() + "'";
            DataView View = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments());
            /*
            SqlDataSource1.SelectCommand = "SELECT * FROM Users";
            DataView DV = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments());

            foreach (DataRowView DRV in DV)
            {
                if (DRV["userEmail"].ToString() != null && DRV["userEmail"].ToString() != "")
                {
                    MailMessage message = new MailMessage();
                    message.From = new MailAddress("noreply@africanhoneybee.net", "AHB Webmaster");
                    message.To.Add(DRV["userEmail"].ToString());
                    message.Subject = "AHB Cleanup Notification";
                    message.Body = Message;
                    message.Attachments.Add(new Attachment(Server.MapPath("~/" + GUID + ".csv")));
                    smtp.Send(message);
                }
            }
             * */
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            CleanupDB();
            //UploadStats();
            //File.Delete(Server.MapPath("~/" + GUID + ".csv"));
        }
    }
}